var express = require('express');
var router = express.Router();
var connection = require('../mysql/mysql');

/* 查询轮播图 */
router.get('/querySlideList', function (req, res, next) {
  const sql = 'SELECT * FROM b_slide WHERE isShow = 1';
  connection.query(sql, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: null,
        success: false,
        message: '查询失败',
      })
    }
    res.json({
      code: 0,
      data: result,
      success: true,
      message: '查询成功',
    })
  });
});

/* 查询公告 */
router.get('/queryBulletinList', function (req, res, next) {
  const sql = 'SELECT * FROM b_bulletin ORDER BY createTime DESC LIMIT 0, 1';
  connection.query(sql, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: null,
        success: false,
        message: '查询失败',
      })
    }
    res.json({
      code: 0,
      data: result.length > 0 ? result[0] : [],
      success: true,
      message: '查询成功',
    })
  });
});

/* 查询文章列表 */
router.post('/queryArticleList', function (req, res, next) {
  let param = req.body;
  let startPage = (Number(param.pageNum) - 1) * param.pageSize;
  let sql = "";
  let sqlTotal = "";
  if (param.classify) {
    sql = `SELECT * FROM b_article WHERE classify = '${param.classify}' AND isShow = 1 ORDER BY createTime DESC LIMIT ${startPage}, ${Number(param.pageSize)}`;
    sqlTotal = `SELECT COUNT(*) FROM b_article WHERE classify = '${param.classify}' AND isShow = 1`;
  } else {
    sql = `SELECT * FROM b_article WHERE isShow = 1 ORDER BY createTime DESC LIMIT ${startPage}, ${Number(param.pageSize)}`;
    sqlTotal = `SELECT COUNT(*) FROM b_article WHERE isShow = 1`;
  }
  connection.query(sql, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: null,
        success: false,
        message: '查询失败',
      })
    }
    result.forEach(item => {
      item.tags = item.tags.split(';');
    });
    connection.query(sqlTotal, (err1, result1) => {
      let total = result1[0]['COUNT(*)'];
      res.json({
        code: 0,
        data: {
          rows: result,
          total: Number(total)
        },
        success: true,
        message: '查询成功',
      })
    });
  });
});

/* 根据id查询文章详情 */
router.get('/queryArticleDetail/:id', function (req, res, next) {
  let id = req.params.id;
  if (!id) {
    return res.json({
      code: 1,
      data: null,
      success: false,
      message: 'id不能为空',
    })
  }
  const sql = `SELECT * FROM b_article WHERE id = ${Number(id)}`;
  const sql1 = `UPDATE b_article SET view = view + '1' WHERE id = ${Number(id)}`;
  connection.query(`${sql1};${sql}`, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: err,
        success: false,
        message: '查询失败',
      })
    }
    let data = result[1][0];
    data.tags = data.tags.split(';');
    res.json({
      code: 0,
      data: data,
      success: true,
      message: '查询成功',
    })
  });
});

/* 根据id和classify查询文章上一章与下一章 */
router.post('/queryArticlePrevAndNext', function (req, res, next) {
  let param = req.body;
  if (!param.id) {
    return res.json({
      code: 1,
      data: null,
      success: false,
      message: 'id不能为空',
    })
  }
  let sql = `SELECT UNIX_TIMESTAMP(createTime) FROM b_article WHERE id = ${param.id}`;
  connection.query(sql, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: null,
        success: false,
        message: '查询失败',
      })
    }
    let sql1 = `SELECT * FROM b_article AS n WHERE n.classify = '${param.classify}' AND UNIX_TIMESTAMP(n.createTime) < '${result[0]['UNIX_TIMESTAMP(createTime)']}' ORDER BY n.createTime DESC LIMIT 0,1`;
    let sql2 = `SELECT * FROM b_article AS n WHERE n.classify = '${param.classify}' AND UNIX_TIMESTAMP(n.createTime) > '${result[0]['UNIX_TIMESTAMP(createTime)']}' ORDER BY n.createTime ASC LIMIT 0,1`;
    connection.query(`${sql1};${sql2}`, (err1, result1) => {
      if (err) {
        return res.json({
          code: 1,
          data: null,
          success: false,
          message: '查询失败',
        })
      }
      let data = {};
      data.next = result1[0][0] ? result1[0][0] : null;
      data.prev = result1[1][0] ? result1[1][0] : null;
      if(data.next){
        data.next.tags = data.next.tags.split(';')
      }
      if(data.prev){
        data.prev.tags = data.prev.tags.split(';')
      }
      res.json({
        code: 0,
        data: data,
        success: true,
        message: '查询成功',
      })
    });
  });
});

/* 保存用户信息 */
router.post('/saveUserInfo', function (req, res, next) {
  let param = req.body;
  for (let v in param) {
    if (v !== "webUrl" && !param[v]) {
      return res.json({
        code: 1,
        data: null,
        success: false,
        message: v + '不能为空',
      })
    }
  }
  const sql = `SELECT * FROM b_user_info WHERE email = '${param.email}'`;
  connection.query(sql, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: null,
        success: false,
        message: '保存失败',
      })
    }
    if (result.length == 0) {
      let sql1 = `INSERT INTO b_user_info (id, nickName, email, webUrl, avatarImg) VALUES (${null},'${param.nickName}','${param.email}','${param.webUrl}','${param.avatarImg}')`;
      connection.query(sql1, (err, result1) => {
        if (err) {
          return res.json({
            code: 1,
            data: null,
            success: false,
            message: '保存失败',
          })
        }
        const sql2 = `SELECT * FROM b_user_info WHERE id = ${result1.insertId}`;
        connection.query(sql2, (err, result2) => {
          if (err) {
            return res.json({
              code: 1,
              data: null,
              success: false,
              message: '保存失败',
            })
          }
          res.json({
            code: 0,
            data: result2[0],
            success: true,
            message: '保存成功',
          })
        })
      })
    } else {
      let sql3 = `UPDATE b_user_info SET nickName = '${param.nickName}', webUrl = '${param.webUrl}' WHERE email = '${param.email}'`;
      connection.query(sql3, (err, result3) => {
        if (err) {
          return res.json({
            code: 1,
            data: null,
            success: false,
            message: '保存失败',
          })
        }
        if (result[0].nickName != param.nickName) {
          let sql4 = `UPDATE b_comment SET nickName = '${param.nickName}' WHERE userId = '${result[0].id}'`;
          connection.query(sql4, (err, result4) => {
            if (err) {
              return res.json({
                code: 1,
                data: null,
                success: false,
                message: '保存失败',
              })
            }
          })
        }

        let obj = result[0];
        obj.nickName = param.nickName;
        obj.webUrl = param.webUrl;
        res.json({
          code: 0,
          data: obj,
          success: true,
          message: '保存成功',
        })
      })
    }
  });
});

/* 评论 */
router.post('/comment', function (req, res, next) {
  let param = req.body;
  if (!param.content) {
    return res.json({
      code: 1,
      data: null,
      success: false,
      message: '内容不能为空',
    })
  }
  const sql = `INSERT INTO b_comment (id, blogId, targetId, byAiteId, byAiteName, byAiteIsAuthor, content, userId, nickName, avatarImg, isAuthor) VALUES (${null},${param.blogId},${param.targetId},${param.byAiteId},'${param.byAiteName}',${param.byAiteIsAuthor},'${param.content}',${param.userId},'${param.nickName}','${param.avatarImg}',${param.isAuthor})`;
  const sql1 = `UPDATE b_article SET msgs = msgs + '1' WHERE id = ${Number(param.blogId)}`;
  connection.query(`${sql};${sql1}`, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: err,
        success: false,
        message: '提交失败',
      })
    }
    res.json({
      code: 0,
      data: null,
      success: true,
      message: '提交成功',
    })
  });
});

/* 查询评论列表 */
router.get('/getCommentList/:blogId', function (req, res, next) {
  let blogId = req.params.blogId;
  if (!blogId) {
    return res.json({
      code: 1,
      data: null,
      success: false,
      message: 'blogId不能为空',
    })
  }
  const sql = `SELECT * FROM b_comment WHERE blogId = ${blogId}`;
  connection.query(sql, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: null,
        success: false,
        message: '查询失败',
      })
    }
    let list = [];
    let sub = [];
    list = result.filter(item => !item.targetId);
    sub = result.filter(item => item.targetId);

    list.forEach(item => {
      item.sub = [];
      sub.forEach(item1 => {
        if (item.id == item1.targetId) {
          item.sub.push(item1);
        }
      })
    });
    res.json({
      code: 0,
      data: list,
      success: true,
      message: '查询成功',
    })
  });
});

/* 查询tags列表 */
router.get('/queryTags', function (req, res, next) {
  const sql = `SELECT * FROM b_tag`;

  connection.query(sql, (err, result) => {
    if (err) {
      return res.json({
        code: 1,
        data: err,
        success: false,
        message: '查询失败',
      })
    }
    res.json({
      code: 0,
      data: result,
      success: true,
      message: '查询成功',
    })
  });
});

module.exports = router;
